﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using LevenBlog.Core.Entities;
using LevenBlog.Core.Utils;

namespace LevenBlog.Core.Data.Sqlite {
    public sealed class SqliteBlog : IBlog {
        public int Count(IDbProvider provider, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provider, sql));
        }

        public int CountFromCategoryID(IDbProvider provider, int categoryid, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState, string.Format("[categoryid]={0}", categoryid)));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provider, sql));
        }

        public int CountFromSearchTitle(IDbProvider provider, string searchString, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState, string.Format("[title] like '%{0}%'", searchString)));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provider, sql));
        }

        public int CountFromSearchTags(IDbProvider provider, string searchTags, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState, string.Format("[tags] like '%{0}%'", searchTags)));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provider, sql));
        }

        public int CountFromDateTime(IDbProvider provider, DateTime startTime, DateTime endTime, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState, string.Format("[posttime] > '{0}' and [posttime] < '{1}'", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"))));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provider, sql));
        }

        public int CountFromUser(IDbProvider provder, string postuser, BlogState selectState) {
            string sql = string.Format("select count([id]) from [lb_blog] {0}", CreateWhereStringWithWhere(selectState, string.Format("[postuser]='{0}'", postuser)));
            return Convert.ToInt32(SqliteHelper.Instance.ExecuteScalar(provder, sql));
        }

        public void Insert(IDbProvider provider, lb_blog blog) {
            IDataParameter[] paras = null;
            string sql = SqliteHelper.Instance.CreateInsertSql(blog, out paras);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql, paras);
        }
        public void Update(IDbProvider provider, lb_blog blog) {
            IDataParameter[] paras = null;
            string sql = SqliteHelper.Instance.CreateUpdateSql(blog, out paras);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql, paras);
        }

        public void Delete(IDbProvider provider, int id) {
            string sql = string.Format("delete from [lb_blog] where [id]={0}", id);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        public void DeleteFromCategoryID(IDbProvider provider, int categoryid) {
            string sql = string.Format("delete from [lb_blog] where [categoryid]={0}", categoryid);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        public void DeleteFromDateTime(IDbProvider provider, DateTime startTime, DateTime endTime) {
            string sql = string.Format("delete from [lb_blog] where [posttime] > '{0}' and [posttime] < '{1}'", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        public lb_blog Get(IDbProvider provider, int id) {
            string sql = string.Format("select * from [lb_blog] where [id]={0}", id);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToEntity<lb_blog>(dr);
            }
        }

        public IList<lb_blog> GetFromCategory(IDbProvider provider, int categoryid) {
            string sql = string.Format("select * from [lb_blog] where [categoryid]={0}", categoryid);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> Get(IDbProvider provider, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> Get(IDbProvider provider, int categoryid, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState, string.Format("[categoryid]={0}", categoryid)), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState, string.Format("[categoryid]={0}", categoryid)), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> Get(IDbProvider provider, DateTime startTime, DateTime endTime, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState, string.Format("[posttime] between '{0}' and '{1}'", startTime, endTime)), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState, string.Format("[posttime] > '{0}' and [posttime] < '{1}'", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"))), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> SearchFromTitle(IDbProvider provider, string searchString, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState, string.Format("[title] like '%{0}%'", searchString)), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState, string.Format("[title] like '%{0}%'", searchString)), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> SearchFromTags(IDbProvider provider, string searchTags, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState, string.Format("[tags] like '%{0}%'", searchTags)), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState, string.Format("[tags] like '%{0}%'", searchTags)), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public IList<lb_blog> GetFromUser(IDbProvider provider, string postuser, int pageSize, int currentPage, BlogState selectState) {
            if (currentPage < 1)
                currentPage = 1;
            //string sql = PageHelper.BuildPageSql(pageSize, currentPage, "*", "[lb_blog]", CreateWhereString(selectState, string.Format("[postuser]='{0}'", postuser)), "[id]", " order by [istop] desc, [id] desc");
            string sql = string.Format("select * from [lb_blog] where {0} order by [istop] desc, [id] desc limit {1},{2}", CreateWhereString(selectState, string.Format("[postuser]='{0}'", postuser)), (currentPage - 1) * pageSize, pageSize);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                return SqliteHelper.Instance.ConvertToList<lb_blog>(dr);
            }
        }

        public Pair<lb_blog, lb_blog> GetAbout(IDbProvider provider, int id) {
            //string preSql = string.Format("select id,title from [lb_blog] where [id] = (select MAX([id]) from [lb_blog] where [id]<{0})", id);
            //string nextSql = string.Format("select id,title from [lb_blog] where [id] = (select MIN([id]) from [lb_blog] where [id]>{0})", id);
            //Pair<lb_blog, lb_blog> aboutAbout = new Pair<lb_blog, lb_blog>();
            //lb_blog preblog = null;
            //lb_blog nextblog = null;
            //using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, preSql))
            //{
            //    preblog = SqliteHelper.Instance.ConvertSomeToEntity<lb_blog>(dr);
            //}
            //using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, nextSql))
            //{
            //    nextblog = SqliteHelper.Instance.ConvertSomeToEntity<lb_blog>(dr);
            //}
            //aboutAbout.First = preblog;
            //aboutAbout.Second = nextblog;
            //return aboutAbout;
            string sql = string.Format("select id,title from [lb_blog] where [id] = (select MAX([id]) from [lb_blog] where [id]<{0}) or [id] = (select MIN([id]) from [lb_blog] where [id]>{0})", id);
            Pair<lb_blog, lb_blog> aboutBlogs = new Pair<lb_blog, lb_blog>();
            aboutBlogs.First = new lb_blog();
            aboutBlogs.Second = new lb_blog();
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                if (dr.Read()) {
                    int firstid = Convert.ToInt32(dr["id"]);
                    if (firstid > id) {
                        aboutBlogs.Second.id = Convert.ToInt32(dr["id"]);
                        aboutBlogs.Second.title = Convert.ToString(dr["title"]);
                    } else {
                        aboutBlogs.First.id = Convert.ToInt32(dr["id"]);
                        aboutBlogs.First.title = Convert.ToString(dr["title"]);
                    }
                }
                if (dr.Read()) {
                    aboutBlogs.Second.id = Convert.ToInt32(dr["id"]);
                    aboutBlogs.Second.title = Convert.ToString(dr["title"]);
                }
            }
            return aboutBlogs;
        }

        public IList<int> GetWriteDays(IDbProvider provider, int year, int month) {
            IList<int> data = new List<int>();
            string sql = string.Format("select [id],[posttime] from [lb_blog] where [isshow]=1 and strftime('%Y',[posttime],'localtime')='{0}' and strftime('%m',[posttime],'localtime')='{1:D2}'", year, month);
            using (IDataReader dr = SqliteHelper.Instance.ExecuteDataReader(provider, sql)) {
                while (dr.Read()) {
                    data.Add(Convert.ToDateTime(dr["posttime"]).Day);
                }
            }
            return data;
        }

        public void AddHits(IDbProvider provider, int id, int num) {
            string sql = String.Format("update [lb_blog] set [hits]=[hits]+{0} where [id]={1}", num, id);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        public void AddCommentNum(IDbProvider provider, int id, int num) {
            string sql = String.Format("update [lb_blog] set [commentnum]=[commentnum]+{0} where [id]={1}", num, id);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        public void AddTrackNum(IDbProvider provider, int id, int num) {
            string sql = String.Format("update [lb_blog] set [tracknum]=[tracknum]+{0} where [id]={1}", num, id);
            SqliteHelper.Instance.ExecuteNonQuery(provider, sql);
        }

        private string CreateWhereString(BlogState selectState) {
            string whereString = string.Empty;
            List<string> whereList = new List<string>();
            if ((BlogState.Show & selectState) == BlogState.Show)
                whereList.Add("[isshow]=1");
            if ((BlogState.UnShow & selectState) == BlogState.UnShow)
                whereList.Add("[isshow]=0");
            if ((BlogState.Lock & selectState) == BlogState.Lock)
                whereList.Add("[islock]=1");
            if ((BlogState.UnLock & selectState) == BlogState.UnLock)
                whereList.Add("[islock]=0");
            if ((BlogState.Top & selectState) == BlogState.Top)
                whereList.Add("[istop]=1");
            if ((BlogState.UnTop & selectState) == BlogState.UnTop)
                whereList.Add("[istop]=0");
            if (whereList.Count > 0) {
                foreach (string w in whereList) {
                    if (string.IsNullOrEmpty(whereString))
                        whereString = w;
                    else
                        whereString += " and " + w;
                }
            }
            return whereString;
        }

        private string CreateWhereStringWithWhere(BlogState selectState) {
            string whereString = CreateWhereString(selectState);
            if (!string.IsNullOrEmpty(whereString))
                return "where " + whereString;
            else
                return string.Empty;
        }

        private string CreateWhereString(BlogState selectState, string innserWhereString) {
            string whereString = CreateWhereString(selectState);
            if (string.IsNullOrEmpty(whereString))
                whereString = innserWhereString;
            else
                whereString += " and " + innserWhereString;
            return whereString;
        }

        private string CreateWhereStringWithWhere(BlogState selectState, string innserWhereString) {
            string whereString = CreateWhereString(selectState, innserWhereString);
            if (!string.IsNullOrEmpty(whereString))
                return "where " + whereString;
            else
                return string.Empty;
        }
    }
}
